﻿using System;
using System.Collections.Generic;
using System.Data;
using PACS.Data;
using WoSoft.PACS.Data.Models;

namespace WoSoft.PACS.Data.CheckData
{
    /// <summary>
    /// 检查申请操作
    /// </summary>
    public class CheckApp : Db
    {
        /// <summary>
        /// 创建检查申请
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string CreateCheckApp(Models.CheckAppModel model)
        {
            var sp_name = "p_检查申请_insert";
            return this.SqlDatabase.StoredProcedure(sp_name)
                .Parameter("个人ID", model.PatentID)
                .Parameter("姓名", model.Name)
                .Parameter("简码", model.Code)
                .Parameter("性别", model.Sex)
                .Parameter("年龄", model.Age)
                .Parameter("年龄单位", model.AgeUnit)
                .Parameter("出生日期", model.Birthday)
                .Parameter("身高", model.Height)
                .Parameter("体重", model.Weight)
                .Parameter("床号", model.BedNum)
                .Parameter("检查类型", model.CheckType)
                .Parameter("检查项目名称", model.CheckItemName)
                .Parameter("检查部位方法", model.CheckPositionMothod)
                .Parameter("申请来源", model.AppSource)
                .Parameter("申请条码", model.ApplyCode)
                .Parameter("申请医生", model.CheckAppDoctor)
                .Parameter("申请科室ID", model.CheckAppDepartmentID)
                .Parameter("申请科室", model.CheckAppDepartment)
                .Parameter("执行科室ID", model.ExecuteDepartmentID)
                .Parameter("是否报到", model.IsBD)
                .Parameter("是否急诊", model.IsEmergency)
                .Parameter("检查目的", model.CheckAppTaget)
                .Parameter("临床诊断", model.Diagnosis)
                .Parameter("注意事项", model.Remark)
                .Parameter("单据性质", model.OrgderProperties)
                .Parameter("操作人", model.Operation)
                .Parameter("检查医生", model.CheckDoctor)
                .Parameter("接口项目代码",model.InterFaceCode)
                .Parameter("录入人员", model.OperationUser)
                .Parameter("申请时间", model.CheckAppTime)
                .QuerySingle<string>();
        }

        /// <summary>
        /// 修改检查申请
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string EditCheckApp(Models.CheckAppModel model)
        {
            var sp_name = "p_检查申请_update";
            return this.SqlDatabase.StoredProcedure(sp_name)
                .Parameter("ID",model.id)
                .Parameter("个人ID", model.PatentID)
                .Parameter("姓名", model.Name)
                .Parameter("简码", model.Code)
                .Parameter("性别", model.Sex)
                .Parameter("年龄", model.Age)
                .Parameter("年龄单位", model.AgeUnit)
                .Parameter("出生日期", model.Birthday)
                .Parameter("身高", model.Height)
                .Parameter("体重", model.Weight)
                .Parameter("床号", model.BedNum)
                .Parameter("检查类型", model.CheckType)
                .Parameter("检查项目名称", model.CheckItemName)
                .Parameter("检查部位方法", model.CheckPositionMothod)
                .Parameter("申请来源", model.AppSource)
                .Parameter("申请条码", model.ApplyCode)
                .Parameter("申请医生", model.CheckAppDoctor)
                .Parameter("申请科室ID", model.CheckAppDepartmentID)
                .Parameter("申请科室", model.CheckAppDepartment)
                .Parameter("执行科室ID", model.ExecuteDepartmentID)
                .Parameter("是否报到", model.IsBD)
                .Parameter("是否急诊", model.IsEmergency)
                .Parameter("检查目的", model.CheckAppTaget)
                .Parameter("临床诊断", model.Diagnosis)
                .Parameter("注意事项", model.Remark)
                .Parameter("单据性质", model.OrgderProperties)
                .Parameter("操作人", model.Operation)
                .Parameter("检查医生", model.CheckDoctor)
                .Parameter("接口项目代码", model.InterFaceCode)
                .Parameter("录入人员", model.OperationUser)
                .Parameter("申请时间", model.CheckAppTime)
                .QuerySingle<string>();
        }

        //返回数据行,根据申请ID
        public DataRow GetCheckAppByID(string id)
        {
            var sql = "select a.*,b.检查号,b.诊室ID 执行科室ID,b.检查技师,b.审核医师 from 检查申请记录 a left join 检查执行记录 b on a.ID=b.申请ID where id =@id";
            var dt = this.SqlDatabase.Sql(sql, null)
                .Parameter("id", id)
                .QuerySingle<DataTable>();
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0];
            }

            return null;
        }

        //获取检查数据
        public DataTable GetCheckList()
        {
            var sql = "SELECT t.ID , t.申请单号 , t.姓名 , t.性别 , (t.年龄 + t.年龄单位) AS 年龄, t.是否急诊,  " +
                "( CASE ( t.状态 )  WHEN 1 THEN '已申请' WHEN 2 THEN '已报到'  WHEN 3 THEN '执行中'  WHEN 4 THEN '已完成' ELSE '' END ) AS 状态 ," +
                "t.申请科室 ,    t.申请医生 ,    t.申请时间 ,    t.是否急诊 ,    e.报到时间 ,    e.是否阳性 ,    e.是否打印 ," +
                "e.已有图像 FROM    检查申请记录 t    LEFT JOIN 检查执行记录 e ON t.ID = e.申请ID";

            return this.SqlDatabase.Sql(sql, null).QuerySingle<DataTable>();
        }

        /// <summary>
        /// 获取检查数据信息
        /// </summary>
        public DataTable GetCheckList(string departmentid, string checkNum, string appNum,
            string name, DateTime startTime, DateTime endTime,
            int ckApp, int ckbd, int ckexe, int ckfinish)
        {
            var spName = "p_查询检查信息_select";
            return this.SqlDatabase.StoredProcedure(spName)
                  .Parameter("已申请状态", ckApp)
                  .Parameter("已报到状态", ckbd)
                  .Parameter("执行中状态", ckexe)
                  .Parameter("已完成状态", ckfinish)
                  .Parameter("姓名", (name == string.Empty) ? DBNull.Value : (object)name)
                  .Parameter("检查号", checkNum == string.Empty ? DBNull.Value : (object)checkNum)
                  .Parameter("申请单号", appNum == string.Empty ? DBNull.Value : (object)appNum)
                  .Parameter("科室ID", departmentid)
                  .Parameter("申请开始日期", startTime)
                  .Parameter("申请结束日期", endTime)
                  .QuerySingle<DataTable>();
        }

        /// <summary>
        /// 查询检查结果信息
        /// </summary>
        public DataTable GetCheckListResult(DateTime startTime, DateTime endTime, string departmentid,
             int ckexe, int ckfinish, string patientID, string patientName,
             int yingx, int yangx, int ysh, int wsh)
        {
            var spName = "p_查询检查结果信息";
            return this.SqlDatabase.StoredProcedure(spName)
                  .Parameter("申请开始日期", startTime)
                  .Parameter("申请结束日期", endTime)
                  .Parameter("申请科室ID", departmentid)
                  .Parameter("执行中状态", ckexe)
                  .Parameter("已完成状态", ckfinish)
                  .Parameter("病人ID", (patientID == string.Empty) ? DBNull.Value : (object)patientID)
                  .Parameter("病人姓名", (patientName == string.Empty) ? DBNull.Value : (object)patientName)
                  .Parameter("阴性", yingx)
                  .Parameter("阳性", yangx)
                  .Parameter("已审核", ysh)
                  .Parameter("未审核", wsh)
                  .QuerySingle<DataTable>();
        }


        /// <summary>
        /// 查询检查结果信息
        /// </summary>
        public DataTable GetCheckListResult_Gzl(DateTime startTime, DateTime endTime, string departmentid)
        {
            var spName = "p_查询检查结果信息_Gzl";
            return this.SqlDatabase.StoredProcedure(spName)
                  .Parameter("申请开始日期", startTime)
                  .Parameter("申请结束日期", endTime)
                  .Parameter("申请科室ID", departmentid)
                  .QuerySingle<DataTable>();
        }

        /// <summary>
        /// 查询检查结果记录
        /// </summary>
        public DataTable GetCheckListResult(DateTime startTime, DateTime endTime, string applydepartmentid, string patientID, string patientName,
            string exedepartmentid, int sq, int bd, int zx, int wc, string jcxmmc)
        {
            var spName = "p_查询检查结果记录";
            return this.SqlDatabase.StoredProcedure(spName)
                  .Parameter("申请开始日期", startTime)
                  .Parameter("申请结束日期", endTime)
                  .Parameter("申请科室ID", applydepartmentid)
                  .Parameter("病人ID", (patientID == string.Empty) ? DBNull.Value : (object)patientID)
                  .Parameter("病人姓名", (patientName == string.Empty) ? DBNull.Value : (object)patientName)
                  .Parameter("执行科室ID", exedepartmentid)
                  .Parameter("已申请状态", sq)
                  .Parameter("已报到状态", bd)
                  .Parameter("执行中状态", zx)
                  .Parameter("已完成状态", wc)
                  .Parameter("检查项目名称", jcxmmc)
                  .QuerySingle<DataTable>();
        }


        /// <summary>
        /// 检查报告
        /// </summary>
        public DataTable GetReportResult(string appId)
        {
            var spName = "p_检查报告";
            return this.SqlDatabase.StoredProcedure(spName)
                  .Parameter("ID", appId)
                  .QuerySingle<DataTable>();
        }

        public int ImgCounts(string appId)
        {
            var sql = "select count(1) from [检查图像记录] where 申请ID=@sqlType";
            var dt = this.SqlDatabase.Sql(sql)
                .Parameter("sqlType", appId)
                .QuerySingle<DataTable>();

            return Convert.ToInt32(dt.Rows[0][0]);
        }

        //检查报到
        public void CheckAppBaodao(string appId, string departmentid, string doctor,string bdr)
        {
            this.SqlDatabase.StoredProcedure("p_检查申请_报到")
                .Parameter("申请ID", appId)
                .Parameter("执行科室ID", departmentid)
                .Parameter("检查医师", doctor)
                .Parameter("报到人", bdr)
                .Execute();
        }

        //保存检查图像到数据库中
        public int SaveImage_Temp(string fileID, string appid, byte[] data)
        {
            //更新有图像状态
            this.SqlDatabase.Update("检查执行记录")
                .Column("已有图像", true)
                .Where("申请ID", appid)
                .Execute();

            var systime = this.SqlDatabase.Sql("SELECT GETDATE()").QuerySingle<DateTime>();
            return this.SqlDatabase.Insert("检查图像记录_临时")
                .Column("ID", fileID)
                .Column("申请ID", appid)
                .Column("检查图像", data, FluentData.DataTypes.Binary)
                .Column("记录时间", systime, FluentData.DataTypes.DateTime)
                .Execute();
        }

        //保存检查图像到数据库中
        public int SaveImage(string fileID, string appid, byte[] data)
        {
            //更新有图像状态
            this.SqlDatabase.Update("检查执行记录")
                .Column("已有图像", true)
                .Where("申请ID", appid)
                .Execute();

            var systime = this.SqlDatabase.Sql("SELECT GETDATE()").QuerySingle<DateTime>();
            return this.SqlDatabase.Insert("检查图像记录")
                .Column("ID", fileID)
                .Column("申请ID", appid)
                .Column("检查图像", data, FluentData.DataTypes.Binary)
                .Column("记录时间", systime, FluentData.DataTypes.DateTime)
                .Execute();
        }

        //保存检查图像到数据库中
        public int CopyImage(string appid, string fileID)
        {
            //更新有图像状态
            this.SqlDatabase.Update("检查执行记录")
                .Column("已有图像", true)
                .Where("申请ID", appid)
                .Execute();

            var appFormIDObj = this.SqlDatabase.Sql("insert 检查图像记录 select * from 检查图像记录_临时 where 申请ID=@appid AND ID = @fileID")
                .Parameter("appid", appid)
                .Parameter("fileID", fileID)
                .Execute();

            return appFormIDObj;
        }

        //获取检查病人的图像列表
        public List<CheckAppImageModel> GetImage(string appid)
        {
            var dt = this.SqlDatabase.Sql("select * from 检查图像记录 where 申请ID=@申请ID order by 记录时间 desc", null)
                .Parameter("申请ID", appid)
                .QuerySingle<DataTable>();
            if (dt != null && dt.Rows.Count > 0)
            {
                List<CheckAppImageModel> imgList = new List<CheckAppImageModel>();
                foreach (DataRow row in dt.Rows)
                {
                    var imgBytes = row["检查图像"] as byte[];
                    var fileName = row["文件名称"] as string;
                    var id = row["ID"] as string;
                    var imgPath = row["图像路径"] as string;
                    var dtime = row["记录时间"] != DBNull.Value ? Convert.ToDateTime(row["记录时间"]) : DateTime.MinValue;

                    CheckAppImageModel model = new CheckAppImageModel()
                    {
                        ID = id,
                        FileName = fileName,
                        ImageBytes = imgBytes,
                        ImagePath = imgPath,
                        CaptureTime = dtime
                    };
                    imgList.Add(model);
                }

                return imgList;
            }

            return null;
        }

        //获取检查病人的图像列表
        public List<CheckAppImageModel> GetImage_Temp(string appid)
        {
            var dt = this.SqlDatabase.Sql("select * from 检查图像记录_临时 where 申请ID=@申请ID order by 记录时间 desc", null)
                .Parameter("申请ID", appid)
                .QuerySingle<DataTable>();
            if (dt != null && dt.Rows.Count > 0)
            {
                List<CheckAppImageModel> imgList = new List<CheckAppImageModel>();
                foreach (DataRow row in dt.Rows)
                {
                    var imgBytes = row["检查图像"] as byte[];
                    var fileName = row["文件名称"] as string;
                    var id = row["ID"] as string;
                    var imgPath = row["图像路径"] as string;
                    var dtime = row["记录时间"] != DBNull.Value ? Convert.ToDateTime(row["记录时间"]) : DateTime.MinValue;

                    CheckAppImageModel model = new CheckAppImageModel()
                    {
                        ID = id,
                        FileName = fileName,
                        ImageBytes = imgBytes,
                        ImagePath = imgPath,
                        CaptureTime = dtime
                    };
                    imgList.Add(model);
                }

                return imgList;
            }

            return null;
        }

        //删除数据库中采集图像
        public int DeleteImage(string imgId)
        {
            var appFormIDObj = this.SqlDatabase.Sql("select 申请ID from 检查图像记录 where ID = @ID")
                .Parameter("ID", imgId)
                .QuerySingle<string>();

            if (!string.IsNullOrEmpty(appFormIDObj))
            {
                //执行删除
                var exeRt = this.SqlDatabase.Delete("检查图像记录")
                    .Where("ID", imgId)
                    .Execute();


                //检查如果删除所有图像,则更新为无图像状态
                this.SqlDatabase.Sql("UPDATE 检查执行记录 SET 已有图像=0 WHERE NOT EXISTS (SELECT ID FROM dbo.检查图像记录 WHERE 申请ID =@申请ID) AND 申请ID =@申请ID", null)
                   .Parameter("申请ID", appFormIDObj)
                   .Execute();

                return exeRt;
            }

            return 0;
        }

        //删除数据库中采集图像
        public int DeleteImage(string imgId, string _checkappID)
        {

                //执行删除
                var exeRt = this.SqlDatabase.Delete("检查图像记录")
                    .Where("ID", imgId)
                    .Where("申请ID", _checkappID)
                    .Execute();


                //检查如果删除所有图像,则更新为无图像状态
                this.SqlDatabase.Sql("UPDATE 检查执行记录 SET 已有图像=0 WHERE NOT EXISTS (SELECT ID FROM dbo.检查图像记录 WHERE 申请ID =@申请ID) AND 申请ID =@申请ID", null)
                   .Parameter("申请ID", _checkappID)
                   .Execute();

                return exeRt;
        }

        //删除数据库中采集图像
        public int DeleteImage_Temp(string imgId, string _checkappID)
        {

            //执行删除
            var exeRt = this.SqlDatabase.Delete("检查图像记录_临时")
                .Where("ID", imgId)
                .Where("申请ID", _checkappID)
                .Execute();


            //检查如果删除所有图像,则更新为无图像状态
            this.SqlDatabase.Sql("UPDATE 检查执行记录 SET 已有图像=0 WHERE NOT EXISTS (SELECT ID FROM dbo.检查图像记录 WHERE 申请ID =@申请ID) AND 申请ID =@申请ID", null)
               .Parameter("申请ID", _checkappID)
               .Execute();

            return exeRt;
        }

        //删除数据库中采集图像公共区域
        public void DeletePublicImage()
        {
            this.SqlDatabase.Sql("delete 检查图像记录 where ID not in(select top 10 ID from 检查图像记录 where isnull(申请ID,'')=''  order by 记录时间 desc) and isnull(申请ID,'')=''", null)
               .Execute();
        }

        //删除数据库中采集图像公共区域
        public void DeletePublicImageAll()
        {
            this.SqlDatabase.Sql("delete 检查图像记录 where isnull(申请ID,'')=''", null)
               .Execute();
        }

        //保存报告数据库中
        public void SaveReport(string _checkappID, string xml, string user ,string jcyx,string zdjy,string jcbz)
        {
            //检查申请记录
            //更新状态为执行中状态
            this.SqlDatabase.Update("检查申请记录")
                .Column("状态", 3)
                .Where("ID", _checkappID)
                .Execute();

            //检查执行记录
            this.SqlDatabase.Update("检查执行记录")
                .Column("诊断医师", user)
                .Column("诊断时间", DateTime.Now)
                .Where("申请ID", _checkappID)
                .Execute();

            //检查报告记录
            //检查数据库中是否存在报告,如果不存在插入内容,否则更新
            var checkSql = "select count(1) as 数量 from 检查报告记录 where 检查ID = @检查ID";
            var cnt = this.SqlDatabase.Sql(checkSql).Parameter("检查ID", _checkappID).QuerySingle<int>();
            if (cnt == 0)
            {//插入
                this.SqlDatabase.Insert("检查报告记录")
                    .Column("检查ID", _checkappID)
                    .Column("文档状态", 1)
                    .Column("创建人", user)
                    .Column("创建时间", DateTime.Now)
                    .Column("报告内容", xml)
                    .Column("检查印象", jcyx)
                    .Column("诊断建议", zdjy)
                    .Column("检查备注", jcbz)
                    .Execute();
            }
            else
            {//更新报告
                this.SqlDatabase.Update("检查报告记录")
                    .Column("修改人", user)
                    .Column("修改时间", DateTime.Now)
                    .Column("报告内容", xml)
                    .Column("检查印象", jcyx)
                    .Column("诊断建议", zdjy)
                    .Column("检查备注", jcbz)
                    .Where("检查ID", _checkappID)
                    .Execute();
            }
        }

        //获取检查报告
        public dynamic GetCheckReport(string _checkappID)
        {
            var sql = "select * from 检查报告记录 where 检查ID=@检查ID";
            var obj = this.SqlDatabase.Sql(sql)
                .Parameter("检查ID", _checkappID)
                .QuerySingle<dynamic>();
            return obj;
        }

        //获取检查申请记录
        public dynamic GetAppCheckInfo(string _checkappID)
        {
            var sql = "select * from 检查申请记录 where ID=@ID";
            var obj = this.SqlDatabase.Sql(sql)
                .Parameter("ID", _checkappID)
                .QuerySingle<dynamic>();
            return obj;
        }

        //更新阳性阴性
        public void Update_Sfyx(string _checkappID, int _sfyx)
        {
            this.SqlDatabase.Update("检查执行记录")
                .Column("是否阳性", _sfyx)
                .Where("申请ID", _checkappID)
                .Execute();
        }

        //更新打印状态
        public void Update_SfPrint(string _checkappID, int _sfdy)
        {
            this.SqlDatabase.Update("检查执行记录")
                .Column("是否打印", _sfdy)
                .Where("申请ID", _checkappID)
                .Execute();
        }

        //回退
        public void Update_State(string _checkappID, int _state)
        {
            this.SqlDatabase.Update("检查申请记录")
                .Column("状态", _state)
                .Where("ID", _checkappID)
                .Execute();
        }

        //删除所有记录
        public void DeleteAll(string _checkappID)
        {
            this.SqlDatabase.Delete("检查图像记录")
                .Where("申请ID", _checkappID)
                .Execute();

            this.SqlDatabase.Delete("检查报告记录")
                .Where("检查ID", _checkappID)
                .Execute();

            this.SqlDatabase.Delete("检查执行记录")
                .Where("申请ID", _checkappID)
                .Execute();

            this.SqlDatabase.Delete("检查申请记录")
                .Where("ID", _checkappID)
                .Execute();
        }

        //删除检查报告记录
        public void DeleteReport(string _checkappID)
        {
            this.SqlDatabase.Delete("检查报告记录")
                .Where("检查ID", _checkappID)
                .Execute();
        }

        //修改报告审核人，审核日期
        public void UpdateReport(string _checkappID, string _audit, string _auditTime,int _state)
        {
            this.SqlDatabase.Update("检查执行记录")
                .Column("审核医师", _audit)
                .Column("审核时间", _auditTime)
                .Where("申请ID", _checkappID)
                .Execute();

            this.SqlDatabase.Update("检查报告记录")
                .Column("文档状态", _state)
                .Where("检查ID", _checkappID)
                .Execute();
        }

        //删除检查申请记录
        public void DeleteApply(string _checkappID)
        {
            this.SqlDatabase.Delete("检查申请记录")
                .Where("ID", _checkappID)
                .Execute();
        }

        //删除检查执行记录
        public void DeleteCheckJl(string _checkappID)
        {
            this.SqlDatabase.Delete("检查执行记录")
                .Where("申请ID", _checkappID)
                .Execute();
        }


        public string GetKSID(string ksmc)
        {
            var sql = "select * from [部门] where 简称=@sqlType";
            var text = string.Empty;
            var dt = this.SqlDatabase.Sql(sql)
                .Parameter("sqlType", ksmc)
                .QuerySingle<DataTable>();
            if (dt.Rows.Count > 0)
            {
                text = dt.Rows[0]["ID"].ToString();
            }

            return text;
        }

        public string GetYSID(string ysmc)
        {
            var sql = "select * from [医护人员] where 简称=@sqlType";
            var text = string.Empty;
            var dt = this.SqlDatabase.Sql(sql)
                .Parameter("sqlType", ysmc)
                .QuerySingle<DataTable>();
            if (dt.Rows.Count > 0)
            {
                text = dt.Rows[0]["ID"].ToString();
            }

            return text;
        }

        public void Update检查执行记录_诊断时间(string _checkappID,string _zdTime)
        {
            this.SqlDatabase.Sql("update 检查执行记录 set 诊断时间='" + _zdTime + "' where 申请ID='" + _checkappID + "'", null)
               .Execute();
        }


        public bool Exists_检查图像记录(string _checkappID, string _id)
        {
            var sql = "select count(1) 是否存在 from [检查图像记录] where 申请ID=@sqlType1 and ID=@sqlType2";
            var dt = this.SqlDatabase.Sql(sql)
                .Parameter("sqlType1", _checkappID)
                .Parameter("sqlType2", _id)
                .QuerySingle<DataTable>();
            if (Convert.ToInt32(dt.Rows[0][0]) > 0)
                return true;
            else
                return false;
        }
    }
}
